﻿namespace QLPhongKham.Access.BenhNhan
{
    using ConfigDatabase.Class;
    using Npgsql;
    using QLPhongKham.Access;
    using QLPhongKham.BaseClass.BenhNhan;
    using QLPhongKham.BaseClass.VienPhi;
    using System;
    using System.Data;

    public class BNChiDinh
    {
        private ConnectDatabase _condata;
        private NpgsqlConnection _conn;
        private General _xuli;

        public BNChiDinh()
        {
            this._condata = new ConnectDatabase();
            this._xuli = new General(this._condata);
        }

        public BNChiDinh(ConnectDatabase condata)
        {
            this._condata = condata;
            this._xuli = new General(this._condata);
        }

        public bool f_delete_chidinh(dtoBNChiDinh cd)
        {
            if (cd.pSLDATHANHTOAN > 0)
            {
                return false;
            }
            if (this._xuli.f_Update_dulieu(string.Concat(new object[] { "delete from ", this._xuli.pSchema, ".", dtoBNChiDinh.tableName, " where ", dtoBNChiDinh.columnID, "=", cd.pID })) == -1)
            {
                return false;
            }
            return true;
        }

        public decimal f_insert_chidinh(dtoBNThongTinBN ThongTinBN, dtoBNTiepDon TiepDon, dtoBNThongTinBN_LienHe LienHe)
        {
            decimal pIDQL;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            NpgsqlTransaction transaction = this._conn.BeginTransaction();
            try
            {
                if (!ThongTinBN.Update(this._conn) && !ThongTinBN.Insert(this._conn))
                {
                    transaction.Rollback();
                    return 0M;
                }
                TiepDon.pIDQL = long.Parse(this._xuli.f_Get_CapID(dtoBNTiepDon.tableName, dtoBNTiepDon.columnIDQL));
                if (!TiepDon.Insert(this._conn))
                {
                    transaction.Rollback();
                    return 0M;
                }
                DataRow[] rowArray = new BNDanhMuc(this._condata).f_loaddata_bn_dmphong(true).Select("id=" + TiepDon.pIDPHONG.ToString());
                if (rowArray[0]["idvp"].ToString() != "0")
                {
                    dtoBNChiDinh dinh = new dtoBNChiDinh(this._condata);
                    long num = long.Parse(this._xuli.f_Get_CapID());
                    dinh.pDONGIA = decimal.Parse(rowArray[0]["dongia"].ToString());
                    dinh.pID = num;
                    dinh.pIDDOITUONG = TiepDon.pIDDOITUONG;
                    dinh.pIDPHONG = TiepDon.pIDPHONG;
                    dinh.pIDQL = TiepDon.pIDQL;
                    dinh.pIDVP = decimal.Parse(rowArray[0]["idvp"].ToString());
                    dinh.pMABN = ThongTinBN.MABN;
                    dinh.pNGAY = TiepDon.pNGAY;
                    dinh.pSOLUONG = 1;
                    if (!dinh.Insert(this._conn))
                    {
                        transaction.Rollback();
                        return 0M;
                    }
                }
                LienHe.IDQL = TiepDon.pIDQL;
                if (!LienHe.Insert(this._conn))
                {
                    transaction.Rollback();
                    return 0M;
                }
                transaction.Commit();
                pIDQL = TiepDon.pIDQL;
            }
            catch
            {
                transaction.Rollback();
                pIDQL = 0M;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return pIDQL;
        }

        public DataTable f_loaddata_chidinh(long idql, bool laycongkham)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = string.Concat(new object[] { 
                "select a.", dtoBNChiDinh.columnIDQL, " as idql,a.", dtoBNChiDinh.columnID, " as id,a.", dtoBNChiDinh.columnMABN, " as mabn,to_char(", dtoBNChiDinh.columnNGAY, ",'dd/mm/yyyy hh24:mi') as ngay,a.", dtoBNChiDinh.columnIDVP, " as idvp,a.", dtoBNChiDinh.columnSOLUONG, " as soluong,a.", dtoBNChiDinh.columnDONGIA, " as dongia,a.", dtoBNChiDinh.columnSLDATHANHTOAN, 
                " as sldathanhtoan,a.", dtoBNChiDinh.columnSOLUONG, "*a.", dtovpDMVienPhi.columnDONGIA, " as thanhtien,b.", dtovpDMVienPhi.columnMA, " as mavp,b.", dtovpDMVienPhi.columnTEN, " as tenvp,b.", dtovpDMVienPhi.columnDVT, " as dvt from ", str, dtoBNChiDinh.tableName, " a inner join ", str, dtovpDMVienPhi.tableName, 
                " b on a.", dtoBNChiDinh.columnIDVP, "=b.", dtovpDMVienPhi.columnID, " left join ", str, dtoBNDMPhong.tableName, " a2 on a.", dtoBNChiDinh.columnIDVP, "=a2.", dtoBNDMPhong.columnIDVP, " where a.", dtoBNChiDinh.columnIDQL, "=", idql, laycongkham ? "" : (" and a2." + dtoBNDMPhong.columnIDVP + " is null "), 
                " order by a.", dtoBNChiDinh.columnNGAY, " desc"
             });
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public DataTable f_loaddata_chidinh(string mabn, bool laycongkham)
        {
            DataTable table = new DataTable();
            string str = this._condata.Schema + ".";
            string sql = "select a." + dtoBNChiDinh.columnIDQL + " as idql,a." + dtoBNChiDinh.columnID + " as id,a." + dtoBNChiDinh.columnMABN + " as mabn,to_char(" + dtoBNChiDinh.columnNGAY + ",'dd/mm/yyyy hh24:mi') as ngay,a." + dtoBNChiDinh.columnIDVP + " as idvp,a." + dtoBNChiDinh.columnSOLUONG + " as soluong,a." + dtoBNChiDinh.columnDONGIA + " as dongia,a." + dtoBNChiDinh.columnSLDATHANHTOAN + " as sldathanhtoan,a." + dtoBNChiDinh.columnSOLUONG + "*a." + dtovpDMVienPhi.columnDONGIA + " as thanhtien,b." + dtovpDMVienPhi.columnMA + " as mavp,b." + dtovpDMVienPhi.columnTEN + " as tenvp,b." + dtovpDMVienPhi.columnDVT + " as dvt from " + str + dtoBNChiDinh.tableName + " a inner join " + str + dtovpDMVienPhi.tableName + " b on a." + dtoBNChiDinh.columnIDVP + "=b." + dtovpDMVienPhi.columnID + " left join " + str + dtoBNDMPhong.tableName + " a2 on a." + dtoBNChiDinh.columnIDVP + "=a2." + dtoBNDMPhong.columnIDVP + " where a.mabn='" + mabn + "'" + (laycongkham ? "" : (" and a2." + dtoBNDMPhong.columnIDVP + " is null ")) + " order by a." + dtoBNChiDinh.columnNGAY + " desc";
            return this._xuli.f_Get_dulieu(sql).Tables[0];
        }

        public decimal f_update_chidinh(dtoBNTiepDon TiepDon, dtoBNThongTinBN_LienHe LienHe)
        {
            decimal pIDQL;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            NpgsqlTransaction transaction = this._conn.BeginTransaction();
            try
            {
                dtoBNTiepDon don = new dtoBNTiepDon(this._condata);
                dtoBNChiDinh dinh = new dtoBNChiDinh(this._condata);
                don.pIDQL = TiepDon.pIDQL;
                don.Load();
                DataTable table = new BNDanhMuc(this._condata).f_loaddata_bn_dmphong(true);
                DataRow[] rowArray = table.Select("id=" + don.pIDPHONG.ToString());
                if ((rowArray[0]["idvp"].ToString() != "0") && (this._xuli.f_Update_dulieu(string.Concat(new object[] { "delete from ", this._condata.Schema, ".", dtoBNChiDinh.tableName, " where ", dtoBNChiDinh.columnIDVP, "=", rowArray[0]["idvp"].ToString(), " and ", dtoBNChiDinh.columnIDQL, "=", don.pIDQL, " and ", dtoBNChiDinh.columnIDPHONG, "=", don.pIDPHONG }), this._conn) == -1))
                {
                    transaction.Rollback();
                    return 0M;
                }
                if (!TiepDon.Update(this._conn))
                {
                    transaction.Rollback();
                    return 0M;
                }
                rowArray = table.Select("id=" + TiepDon.pIDPHONG.ToString());
                if (rowArray[0]["idvp"].ToString() != "0")
                {
                    dinh = new dtoBNChiDinh(this._condata);
                    long num = long.Parse(this._xuli.f_Get_CapID(dtoBNChiDinh.tableName, dtoBNChiDinh.columnID));
                    dinh.pDONGIA = decimal.Parse(rowArray[0]["dongia"].ToString());
                    dinh.pID = num;
                    dinh.pIDDOITUONG = TiepDon.pIDDOITUONG;
                    dinh.pIDPHONG = TiepDon.pIDPHONG;
                    dinh.pIDQL = TiepDon.pIDQL;
                    dinh.pIDVP = decimal.Parse(rowArray[0]["idvp"].ToString());
                    dinh.pMABN = TiepDon.pMABN;
                    dinh.pNGAY = TiepDon.pNGAY;
                    dinh.pSOLUONG = 1;
                    if (!dinh.Insert(this._conn))
                    {
                        transaction.Rollback();
                        return 0M;
                    }
                }
                if (!LienHe.Update(this._conn) && !LienHe.Insert(this._conn))
                {
                    transaction.Rollback();
                    return 0M;
                }
                transaction.Commit();
                pIDQL = TiepDon.pIDQL;
            }
            catch
            {
                transaction.Rollback();
                pIDQL = 0M;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return pIDQL;
        }
    }
}

